package com.nekonomics
package persistence.query

import domain.detail.OrderDetail

import doobie.*
import doobie.free.connection.ConnectionIO
import doobie.implicits.*
import doobie.postgres.implicits.*
import zio.*


trait OrderDetailRepo {
  def listByMerchantId(merchantId: Long): ConnectionIO[List[OrderDetail]]

}



object OrderDetailRepo {
  val live: ULayer[OrderDetailRepo] = ZLayer.succeed(OrderDetailRepoLive)
}


object OrderDetailRepoLive extends OrderDetailRepo {
  override def listByMerchantId(merchantId: Long): ConnectionIO[List[OrderDetail]] = {
    sql"""
         |with orderIds as (
         |  select
         |    distinct order_item.order_id as order_id
         |  from public.order_item
         |  join public.sku on sku.id = order_item.sku_id
         |  join public.spu on spu.id = sku.spu_id
         |  where spu.merchant_id = $merchantId
         |)
         |
         |select
         |  "order".id,
         |  "user".nickname,
         |  "user".email,
         |  "order".receiver_name,
         |  "order".receiver_phone,
         |  "order".province,
         |  "order".city,
         |  "order".region,
         |  "order".detail_address,
         |  "order".status,
         |  "order".total_price,
         |  "order".created_at
         |from public."order"
         |join orderIds on orderIds.order_id = "order".id
         |join public."user" on "user".id = "order".user_id
         |
         |""".stripMargin
      .query[OrderDetail]
      .to[List]
  }
}

